﻿Imports System.Data.SqlClient
Imports System.Data
Public Class frmPhucHoiDuLieu
    Dim data As New DataProvider
    Dim tableName As New DataTable
    Dim cnn As New SqlConnection
    Private Sub btnServerName_Click(sender As System.Object, e As System.EventArgs) Handles btnServerName.Click
        Dim str As String = "Select * From sys.Servers"
        tableName = data.GetTable(str)
        For i As Integer = 0 To tableName.Rows.Count - 1
            cbServerName.Items.Add(tableName.Rows(i)("name").ToString())
        Next
    End Sub

    Private Sub btnDatabase_Click(sender As System.Object, e As System.EventArgs) Handles btnDatabase.Click
        Dim str As String = "DECLARE @DBuser_sql VARCHAR(4000) DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200)) SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name, a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')ORDER BY Name'INSERT @DBuser_table EXEC sp_MSforeachdb @command1=@dbuser_sql SELECT distinct DBName FROM @DBuser_table ORDER BY DBName"
        tableName = data.GetTable(str)
        For i As Integer = 0 To tableName.Rows.Count - 1
            cbDbName.Items.Add(tableName.Rows(i)("DBName").ToString())
        Next
    End Sub

    Private Sub btnDuongDan_Click(sender As System.Object, e As System.EventArgs) Handles btnDuongDan.Click
        Dim save As New SaveFileDialog
        save.Title = "Chọn nơi sao lưu dữ liệu"
        save.Filter = "Tập tin sao lưu (*.bak)|*.bak"
        save.ShowDialog()
        txtDuongDan.Text = save.FileName
    End Sub

    Private Sub btnPhucHoi_Click(sender As System.Object, e As System.EventArgs) Handles btnPhucHoi.Click
        If cbServerName.Text = "" Then
            MessageBox.Show("Bạn chưa nhập tên máy chủ!", "Error")
        ElseIf cbServerName.Text = "" Then
            MessageBox.Show("Bạn chưa nhập tên database!", "Error")
        ElseIf cbServerName.Text = "" Then
            MessageBox.Show("Bạn chưa chọn đường dẫn!", "Error")
        Else
            Dim str As String = "Data Source=" + cbServerName.Text + ";Initial Catalog=" + cbDbName.Text + ";Integrated Security=True"
            cnn = data.ConnectionData()
            Try
                Dim strBackup As String = "USE MASTER ALTER DATABASE " + cbDbName.Text + " SET Single_User WITH Rollback Immediate ALTER DATABASE " + cbDbName.Text + " SET Multi_User RESTORE DATABASE " + cbDbName.Text + " FROM DISK = '" + txtDuongDan.Text + "' WITH REPLACE "
                Dim sqlCom As SqlCommand = New SqlCommand()
                sqlCom.CommandText = strBackup
                sqlCom.Connection = cnn
                sqlCom.ExecuteNonQuery()
                MessageBox.Show("Dữ liệu của bạn đã được phục hồi thành công!")
                cnn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString(), "Restore database")
            Finally
                cnn.Close()
            End Try
        End If
    End Sub

    Private Sub btnDong_Click(sender As System.Object, e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub
End Class